package org.bugjail.dataaccess.jdbc.objects;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import org.bugjail.data.Project;
import org.bugjail.data.TestReport;
import org.bugjail.dataaccess.jdbc.connection.QueryException;
import org.bugjail.dataaccess.jdbc.connection.QueryManager;
import org.bugjail.dataaccess.objects.ProjectDAO;
import org.bugjail.dataaccess.objects.TestReportDAO;

class JDBCTestReportDAO implements TestReportDAO {
	
	private final String PROPERTIES_FILE = "/org/bugjail/dataaccess/jdbc/properties/testreportdao.properties";
	
	private final String PROPERTY_INSERT = "insert";
	
	private final String PROPERTY_UPDATE = "update";
	
	private final String PROPERTY_DELETE = "delete";
	
	private final String PROPERTY_RETRIEVE_BY_ID = "retrieve.byid";
	private final String PROPERTY_RETRIEVE_ALL = "retrieve.all";
	private final String PROPERTY_RETRIEVE_BY_NAME = "retrieve.byname";
	private final String PROPERTY_RETRIEVE_BY_PROJECT = "retrieve.byproject";

	@Override
	public void create(TestReport element) {
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
				.getPreparedStatement(PROPERTY_INSERT);
				statement.setInt(1, element.getProject().getId());
				statement.setString(2, element.getName());
				statement.setString(3, element.getDescription());
				statement.executeUpdate();
				
				QueryManager.commit(queryManager);
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}

	@Override
	public void delete(TestReport element) {
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
				.getPreparedStatement(PROPERTY_DELETE);
				statement.setInt(1, element.getId());
				statement.executeUpdate();
				QueryManager.commit(queryManager);
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}

	@Override
	public List<TestReport> retrieveAll() {
		List<TestReport> testReports = new LinkedList<TestReport>();
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
				.getPreparedStatement(PROPERTY_RETRIEVE_ALL);
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					testReports.add(extractTestReportFromResultSet(rs, queryManager));
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return testReports;
	}

	@Override
	public TestReport retrieveById(int id) {
		TestReport testReport = new TestReport();
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
				.getPreparedStatement(PROPERTY_RETRIEVE_BY_ID);
				statement.setInt(1, id);
				ResultSet rs = statement.executeQuery();
				if (rs.next()) {
					testReport = extractTestReportFromResultSet(rs, queryManager);
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return testReport;
	}

	@Override
	public List<TestReport> retrieveByName(String name) {
		name = "%"+name+"%";
		List<TestReport> testReports = new LinkedList<TestReport>();
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
				.getPreparedStatement(PROPERTY_RETRIEVE_BY_NAME);
				statement.setString(1, name);
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					testReports.add(extractTestReportFromResultSet(rs, queryManager));
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return testReports;
	}

	@Override
	public void update(TestReport element) {
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
				.getPreparedStatement(PROPERTY_UPDATE);
				statement.setString(1, element.getName());
				statement.setString(2, element.getDescription());
				statement.setInt(3, element.getId());
				statement.executeUpdate();
				
				QueryManager.commit(queryManager);
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}

	private TestReport extractTestReportFromResultSet(ResultSet rs, QueryManager queryManager) throws SQLException {
		TestReport testReport = new TestReport();
		testReport.setId(rs.getInt("id"));
		
		ProjectDAO projDao = JDBCDAOFactory.getDAOFactory().getProjectDAO();
		testReport.setProject(projDao.retrieveById(rs.getInt("project_id")));
		
		testReport.setName(rs.getString("name"));
		testReport.setDescription(rs.getString("description"));
		
		return testReport;
	}

	@Override
	public List<TestReport> retrieveTestReportsOfProject(Project project) {
		List<TestReport> testReports = new LinkedList<TestReport>();
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_RETRIEVE_BY_PROJECT);
				statement.setInt(1, project.getId());
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					testReports.add(extractTestReportFromResultSet(rs,
							queryManager));
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return testReports;
	}

}